{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Class03"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read data and drop duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = '/Users/ml/Google Drive/af/teaching/database/data/'\n",
    "ibes_raw = pd.read_csv(file_path+'ibes_1976_1990_summ_both.txt',sep='\\t',low_memory=False)\n",
    "ibes_raw.columns = ibes_raw.columns.str.lower()\n",
    "ibes_raw = ibes_raw.drop_duplicates(['ticker','statpers']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>cusip</th>\n",
       "      <th>oftic</th>\n",
       "      <th>cname</th>\n",
       "      <th>statpers</th>\n",
       "      <th>measure</th>\n",
       "      <th>fiscalp</th>\n",
       "      <th>fpi</th>\n",
       "      <th>estflag</th>\n",
       "      <th>curcode</th>\n",
       "      <th>numest</th>\n",
       "      <th>numup</th>\n",
       "      <th>numdown</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>02742010</td>\n",
       "      <td>A</td>\n",
       "      <td>AMERN MEDIC BLDG</td>\n",
       "      <td>19831020</td>\n",
       "      <td>EPS</td>\n",
       "      <td>ANN</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>USD</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>02742010</td>\n",
       "      <td>A</td>\n",
       "      <td>AMERN MEDIC BLDG</td>\n",
       "      <td>19831117</td>\n",
       "      <td>EPS</td>\n",
       "      <td>ANN</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>USD</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>02742010</td>\n",
       "      <td>A</td>\n",
       "      <td>AMERN MEDIC BLDG</td>\n",
       "      <td>19831215</td>\n",
       "      <td>EPS</td>\n",
       "      <td>ANN</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>USD</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>02742010</td>\n",
       "      <td>A</td>\n",
       "      <td>AMERN MEDIC BLDG</td>\n",
       "      <td>19860116</td>\n",
       "      <td>EPS</td>\n",
       "      <td>ANN</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>USD</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>02742010</td>\n",
       "      <td>A</td>\n",
       "      <td>AMERN MEDIC BLDG</td>\n",
       "      <td>19860220</td>\n",
       "      <td>EPS</td>\n",
       "      <td>ANN</td>\n",
       "      <td>1</td>\n",
       "      <td>P</td>\n",
       "      <td>USD</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ticker     cusip oftic             cname  statpers measure fiscalp  fpi  \\\n",
       "0      A  02742010     A  AMERN MEDIC BLDG  19831020     EPS     ANN    1   \n",
       "1      A  02742010     A  AMERN MEDIC BLDG  19831117     EPS     ANN    1   \n",
       "2      A  02742010     A  AMERN MEDIC BLDG  19831215     EPS     ANN    1   \n",
       "3      A  02742010     A  AMERN MEDIC BLDG  19860116     EPS     ANN    1   \n",
       "4      A  02742010     A  AMERN MEDIC BLDG  19860220     EPS     ANN    1   \n",
       "\n",
       "  estflag curcode  numest  numup  numdown  \n",
       "0       P     USD       1      0        0  \n",
       "1       P     USD       1      0        0  \n",
       "2       P     USD       1      0        0  \n",
       "3       P     USD       1      0        0  \n",
       "4       P     USD       1      0        0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibes_raw.iloc[:5,:13]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Keep US firms\n",
    "This file contains both US firm and non-US firms. **usfirm** can be used to filter them out: it is US firm if **usfirm** is 1 and it is global firm if **usfirm** equals to 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>usfirm</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>228834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>453924</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ticker\n",
       "usfirm        \n",
       "0       228834\n",
       "1       453924"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibes_raw.groupby('usfirm')[['ticker']].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "453924"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibes_us = ibes_raw[ibes_raw['usfirm']==1].copy()\n",
    "len(ibes_us)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sample selection: keep firms with at least 60 month of numest "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "344326"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibes_us['n_numest'] = ibes_us.groupby('ticker')['numest'].transform('count')\n",
    "ibes_us_1 = ibes_us[ibes_us['n_numest']>=60].copy()\n",
    "ibes_us_1 = ibes_us_1.sort_values(['ticker','statpers']).reset_index(drop=True)\n",
    "len(ibes_us_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check number of unique firms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6951"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ibes_us['ticker'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Basic summary statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>numest</th>\n",
       "      <th>meanest</th>\n",
       "      <th>stdev</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>344326.000000</td>\n",
       "      <td>3.443240e+05</td>\n",
       "      <td>2.857610e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>7.994543</td>\n",
       "      <td>1.674507e+05</td>\n",
       "      <td>5.551247e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>7.634961</td>\n",
       "      <td>1.922401e+07</td>\n",
       "      <td>4.449504e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>-9.882353e+08</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.500000e-01</td>\n",
       "      <td>2.000000e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>5.000000</td>\n",
       "      <td>8.900000e-01</td>\n",
       "      <td>6.000000e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>12.000000</td>\n",
       "      <td>1.820000e+00</td>\n",
       "      <td>1.600000e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>52.000000</td>\n",
       "      <td>2.861765e+09</td>\n",
       "      <td>8.176471e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              numest       meanest         stdev\n",
       "count  344326.000000  3.443240e+05  2.857610e+05\n",
       "mean        7.994543  1.674507e+05  5.551247e+04\n",
       "std         7.634961  1.922401e+07  4.449504e+06\n",
       "min         1.000000 -9.882353e+08  0.000000e+00\n",
       "25%         2.000000  3.500000e-01  2.000000e-02\n",
       "50%         5.000000  8.900000e-01  6.000000e-02\n",
       "75%        12.000000  1.820000e+00  1.600000e-01\n",
       "max        52.000000  2.861765e+09  8.176471e+08"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibes_us_1[['numest','meanest','stdev']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>1976</th>\n",
       "      <th>1977</th>\n",
       "      <th>1978</th>\n",
       "      <th>1979</th>\n",
       "      <th>1980</th>\n",
       "      <th>1981</th>\n",
       "      <th>1982</th>\n",
       "      <th>1983</th>\n",
       "      <th>1984</th>\n",
       "      <th>1985</th>\n",
       "      <th>1986</th>\n",
       "      <th>1987</th>\n",
       "      <th>1988</th>\n",
       "      <th>1989</th>\n",
       "      <th>1990</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">numest</th>\n",
       "      <th>mean</th>\n",
       "      <td>5.692249</td>\n",
       "      <td>5.553678</td>\n",
       "      <td>5.795983</td>\n",
       "      <td>6.074493</td>\n",
       "      <td>6.324035</td>\n",
       "      <td>7.092612</td>\n",
       "      <td>7.719291</td>\n",
       "      <td>7.655167</td>\n",
       "      <td>7.715934</td>\n",
       "      <td>8.418741e+00</td>\n",
       "      <td>8.942773e+00</td>\n",
       "      <td>9.299843e+00</td>\n",
       "      <td>9.701810e+00</td>\n",
       "      <td>1.028249e+01</td>\n",
       "      <td>1.033535e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>median</th>\n",
       "      <td>4.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.000000e+00</td>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>7.000000e+00</td>\n",
       "      <td>7.000000e+00</td>\n",
       "      <td>7.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.465563</td>\n",
       "      <td>5.440635</td>\n",
       "      <td>5.693274</td>\n",
       "      <td>5.721052</td>\n",
       "      <td>5.568066</td>\n",
       "      <td>6.070099</td>\n",
       "      <td>6.889808</td>\n",
       "      <td>7.023749</td>\n",
       "      <td>7.385330</td>\n",
       "      <td>8.110911e+00</td>\n",
       "      <td>8.399494e+00</td>\n",
       "      <td>8.514879e+00</td>\n",
       "      <td>8.877915e+00</td>\n",
       "      <td>9.261380e+00</td>\n",
       "      <td>9.135582e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>29.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>39.000000</td>\n",
       "      <td>4.400000e+01</td>\n",
       "      <td>5.200000e+01</td>\n",
       "      <td>4.400000e+01</td>\n",
       "      <td>4.800000e+01</td>\n",
       "      <td>5.100000e+01</td>\n",
       "      <td>5.000000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">meanest</th>\n",
       "      <th>mean</th>\n",
       "      <td>15.917728</td>\n",
       "      <td>14.478176</td>\n",
       "      <td>27.672631</td>\n",
       "      <td>30.469365</td>\n",
       "      <td>32.559812</td>\n",
       "      <td>31.528427</td>\n",
       "      <td>36.560658</td>\n",
       "      <td>35.041802</td>\n",
       "      <td>31.492565</td>\n",
       "      <td>9.251836e+04</td>\n",
       "      <td>9.528349e+05</td>\n",
       "      <td>8.535299e+05</td>\n",
       "      <td>2.114121e+05</td>\n",
       "      <td>-6.042236e+04</td>\n",
       "      <td>2.177269e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>median</th>\n",
       "      <td>0.850000</td>\n",
       "      <td>0.980000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.070000</td>\n",
       "      <td>1.060000</td>\n",
       "      <td>1.090000</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>0.840000</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>8.200000e-01</td>\n",
       "      <td>7.400000e-01</td>\n",
       "      <td>7.700000e-01</td>\n",
       "      <td>8.600000e-01</td>\n",
       "      <td>8.700000e-01</td>\n",
       "      <td>8.100000e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>387.591476</td>\n",
       "      <td>381.469765</td>\n",
       "      <td>638.814774</td>\n",
       "      <td>706.432692</td>\n",
       "      <td>779.285898</td>\n",
       "      <td>781.877931</td>\n",
       "      <td>845.769494</td>\n",
       "      <td>807.799582</td>\n",
       "      <td>1014.431228</td>\n",
       "      <td>1.590610e+07</td>\n",
       "      <td>4.768792e+07</td>\n",
       "      <td>4.105745e+07</td>\n",
       "      <td>1.469746e+07</td>\n",
       "      <td>1.076343e+07</td>\n",
       "      <td>3.591394e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-4.330000</td>\n",
       "      <td>-3.470000</td>\n",
       "      <td>-100.000000</td>\n",
       "      <td>-12.000000</td>\n",
       "      <td>-87.910000</td>\n",
       "      <td>-2330.000000</td>\n",
       "      <td>-500.000000</td>\n",
       "      <td>-5495.000000</td>\n",
       "      <td>-18256.000000</td>\n",
       "      <td>-2.277600e+04</td>\n",
       "      <td>-4.308000e+03</td>\n",
       "      <td>-5.400000e+02</td>\n",
       "      <td>-1.970587e+08</td>\n",
       "      <td>-9.882353e+08</td>\n",
       "      <td>-7.941176e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>15282.350000</td>\n",
       "      <td>19411.750000</td>\n",
       "      <td>22323.520000</td>\n",
       "      <td>23523.520000</td>\n",
       "      <td>25665.970000</td>\n",
       "      <td>27935.280000</td>\n",
       "      <td>32929.390000</td>\n",
       "      <td>29399.980000</td>\n",
       "      <td>64411.760000</td>\n",
       "      <td>2.735294e+09</td>\n",
       "      <td>2.861765e+09</td>\n",
       "      <td>2.479412e+09</td>\n",
       "      <td>1.214706e+09</td>\n",
       "      <td>5.000000e+08</td>\n",
       "      <td>3.529409e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">stdev</th>\n",
       "      <th>mean</th>\n",
       "      <td>0.747380</td>\n",
       "      <td>0.426487</td>\n",
       "      <td>0.689023</td>\n",
       "      <td>1.522587</td>\n",
       "      <td>3.742299</td>\n",
       "      <td>4.077995</td>\n",
       "      <td>4.235661</td>\n",
       "      <td>4.607995</td>\n",
       "      <td>4.259071</td>\n",
       "      <td>6.948415e+00</td>\n",
       "      <td>1.017789e+05</td>\n",
       "      <td>1.357483e+05</td>\n",
       "      <td>2.393500e+05</td>\n",
       "      <td>1.334710e+05</td>\n",
       "      <td>1.175034e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>median</th>\n",
       "      <td>0.050000</td>\n",
       "      <td>0.040000</td>\n",
       "      <td>0.040000</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>0.060000</td>\n",
       "      <td>0.070000</td>\n",
       "      <td>0.080000</td>\n",
       "      <td>0.070000</td>\n",
       "      <td>0.060000</td>\n",
       "      <td>7.000000e-02</td>\n",
       "      <td>7.000000e-02</td>\n",
       "      <td>7.000000e-02</td>\n",
       "      <td>7.000000e-02</td>\n",
       "      <td>6.000000e-02</td>\n",
       "      <td>6.000000e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>18.388510</td>\n",
       "      <td>9.163009</td>\n",
       "      <td>17.621033</td>\n",
       "      <td>35.940588</td>\n",
       "      <td>88.022080</td>\n",
       "      <td>91.869436</td>\n",
       "      <td>95.290328</td>\n",
       "      <td>98.661827</td>\n",
       "      <td>124.492229</td>\n",
       "      <td>2.202207e+02</td>\n",
       "      <td>5.339098e+06</td>\n",
       "      <td>6.286256e+06</td>\n",
       "      <td>1.155211e+07</td>\n",
       "      <td>5.787088e+06</td>\n",
       "      <td>4.890620e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>735.000000</td>\n",
       "      <td>345.000000</td>\n",
       "      <td>882.350000</td>\n",
       "      <td>1695.000000</td>\n",
       "      <td>4239.100000</td>\n",
       "      <td>4706.810000</td>\n",
       "      <td>7474.530000</td>\n",
       "      <td>5664.710000</td>\n",
       "      <td>7008.000000</td>\n",
       "      <td>1.492400e+04</td>\n",
       "      <td>4.117647e+08</td>\n",
       "      <td>3.382350e+08</td>\n",
       "      <td>8.176471e+08</td>\n",
       "      <td>3.823529e+08</td>\n",
       "      <td>3.058824e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "year                    1976          1977          1978          1979  \\\n",
       "numest  mean        5.692249      5.553678      5.795983      6.074493   \n",
       "        median      4.000000      3.000000      3.000000      4.000000   \n",
       "        std         5.465563      5.440635      5.693274      5.721052   \n",
       "        min         1.000000      1.000000      1.000000      1.000000   \n",
       "        max        29.000000     28.000000     28.000000     28.000000   \n",
       "meanest mean       15.917728     14.478176     27.672631     30.469365   \n",
       "        median      0.850000      0.980000      1.000000      1.070000   \n",
       "        std       387.591476    381.469765    638.814774    706.432692   \n",
       "        min        -4.330000     -3.470000   -100.000000    -12.000000   \n",
       "        max     15282.350000  19411.750000  22323.520000  23523.520000   \n",
       "stdev   mean        0.747380      0.426487      0.689023      1.522587   \n",
       "        median      0.050000      0.040000      0.040000      0.050000   \n",
       "        std        18.388510      9.163009     17.621033     35.940588   \n",
       "        min         0.000000      0.000000      0.000000      0.000000   \n",
       "        max       735.000000    345.000000    882.350000   1695.000000   \n",
       "\n",
       "year                    1980          1981          1982          1983  \\\n",
       "numest  mean        6.324035      7.092612      7.719291      7.655167   \n",
       "        median      4.000000      5.000000      5.000000      5.000000   \n",
       "        std         5.568066      6.070099      6.889808      7.023749   \n",
       "        min         1.000000      1.000000      1.000000      1.000000   \n",
       "        max        28.000000     31.000000     34.000000     36.000000   \n",
       "meanest mean       32.559812     31.528427     36.560658     35.041802   \n",
       "        median      1.060000      1.090000      0.950000      0.840000   \n",
       "        std       779.285898    781.877931    845.769494    807.799582   \n",
       "        min       -87.910000  -2330.000000   -500.000000  -5495.000000   \n",
       "        max     25665.970000  27935.280000  32929.390000  29399.980000   \n",
       "stdev   mean        3.742299      4.077995      4.235661      4.607995   \n",
       "        median      0.060000      0.070000      0.080000      0.070000   \n",
       "        std        88.022080     91.869436     95.290328     98.661827   \n",
       "        min         0.000000      0.000000      0.000000      0.000000   \n",
       "        max      4239.100000   4706.810000   7474.530000   5664.710000   \n",
       "\n",
       "year                    1984          1985          1986          1987  \\\n",
       "numest  mean        7.715934  8.418741e+00  8.942773e+00  9.299843e+00   \n",
       "        median      5.000000  5.000000e+00  6.000000e+00  6.000000e+00   \n",
       "        std         7.385330  8.110911e+00  8.399494e+00  8.514879e+00   \n",
       "        min         1.000000  1.000000e+00  1.000000e+00  1.000000e+00   \n",
       "        max        39.000000  4.400000e+01  5.200000e+01  4.400000e+01   \n",
       "meanest mean       31.492565  9.251836e+04  9.528349e+05  8.535299e+05   \n",
       "        median      0.900000  8.200000e-01  7.400000e-01  7.700000e-01   \n",
       "        std      1014.431228  1.590610e+07  4.768792e+07  4.105745e+07   \n",
       "        min    -18256.000000 -2.277600e+04 -4.308000e+03 -5.400000e+02   \n",
       "        max     64411.760000  2.735294e+09  2.861765e+09  2.479412e+09   \n",
       "stdev   mean        4.259071  6.948415e+00  1.017789e+05  1.357483e+05   \n",
       "        median      0.060000  7.000000e-02  7.000000e-02  7.000000e-02   \n",
       "        std       124.492229  2.202207e+02  5.339098e+06  6.286256e+06   \n",
       "        min         0.000000  0.000000e+00  0.000000e+00  0.000000e+00   \n",
       "        max      7008.000000  1.492400e+04  4.117647e+08  3.382350e+08   \n",
       "\n",
       "year                    1988          1989          1990  \n",
       "numest  mean    9.701810e+00  1.028249e+01  1.033535e+01  \n",
       "        median  7.000000e+00  7.000000e+00  7.000000e+00  \n",
       "        std     8.877915e+00  9.261380e+00  9.135582e+00  \n",
       "        min     1.000000e+00  1.000000e+00  1.000000e+00  \n",
       "        max     4.800000e+01  5.100000e+01  5.000000e+01  \n",
       "meanest mean    2.114121e+05 -6.042236e+04  2.177269e+04  \n",
       "        median  8.600000e-01  8.700000e-01  8.100000e-01  \n",
       "        std     1.469746e+07  1.076343e+07  3.591394e+06  \n",
       "        min    -1.970587e+08 -9.882353e+08 -7.941176e+07  \n",
       "        max     1.214706e+09  5.000000e+08  3.529409e+08  \n",
       "stdev   mean    2.393500e+05  1.334710e+05  1.175034e+05  \n",
       "        median  7.000000e-02  6.000000e-02  6.000000e-02  \n",
       "        std     1.155211e+07  5.787088e+06  4.890620e+06  \n",
       "        min     0.000000e+00  0.000000e+00  0.000000e+00  \n",
       "        max     8.176471e+08  3.823529e+08  3.058824e+08  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibes_us_1['year'] = (ibes_us_1['statpers']/10000).astype(int)\n",
    "ibes_us_1.groupby('year')[['numest','meanest','stdev']].aggregate(['mean','median','std','min','max']).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Percentile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(10,91,10):\n",
    "    ibes_us_1['p'+str(i)] = ibes_us_1.groupby('year')['meanest'].transform(lambda x: x.quantile(i/100))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Correlation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>numest</th>\n",
       "      <th>meanest</th>\n",
       "      <th>stdev</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>numest</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.002907</td>\n",
       "      <td>-0.006359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>meanest</th>\n",
       "      <td>-0.002907</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.515212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stdev</th>\n",
       "      <td>-0.006359</td>\n",
       "      <td>0.515212</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           numest   meanest     stdev\n",
       "numest   1.000000 -0.002907 -0.006359\n",
       "meanest -0.002907  1.000000  0.515212\n",
       "stdev   -0.006359  0.515212  1.000000"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibes_us_1[['numest','meanest','stdev']].corr()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
